import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

data_air_report2_router = APIRouter(prefix="/report",tags=["空气统计"],dependencies=[Depends(oauth2_scheme)])

class MonthlyAirStatisticsSearch(BaseModel):
    """每月空气统计查询请求参数"""
    fsiteNo: str = Field("", description="站点编号")
    date: str = Field("200001", description="统计年月，格式为YYYYMM，默认200001")

@data_air_report2_router.get("/getMonthStatisticsDataByFsiteNo",summary="每月空气统计")
def list_air_report001(search: MonthlyAirStatisticsSearch=Query(),db:Session = Depends(get_db)):
    param ={}
    sql = "select faqi_name,faqi_day,faqi_rate,fhb_day,fhb_rate,ftb_day,ftb_rate"
    sql = sql + " from A_TM_SITE_AIRQUALITY"
    sql = sql + " where FSITE_NO= :site_no "
    param["site_no"] = search.fsiteNo
    sql = sql + " and FMONITER_MONTH= :date "
    param["date"] = search.date
    sql = sql + " order by FAQI_TYPE"
    data = db.execute(text(sql),param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['faqiName'] = data[key][0]
        d1['faqiDay'] = data[key][1]
        d1['faqiRate'] = data[key][2]
        d1['fhbDay'] = data[key][3]
        d1['fhbRate'] = data[key][4]
        d1['ftbDay'] = data[key][5]
        d1['ftbRate'] = data[key][6]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)

class QuarterlyAirStatisticsSearch(BaseModel):
    """每季空气统计查询请求参数"""
    fsiteNo: str = Field("", description="站点编号")
    date: str = Field("2000", description="统计年份，默认2000")
    quarter: str = Field("1", description="统计季度，默认1季度")

@data_air_report2_router.get("/getQuarterStatisticsDataByFsiteNo",summary="每季空气统计")
def list_air_report002(search:QuarterlyAirStatisticsSearch = Query(),db:Session = Depends(get_db)):
    param = {}
    sql = "select faqi_name,faqi_day,faqi_rate,fhb_day,fhb_rate,ftb_day,ftb_rate"
    sql = sql + " from A_TQ_SITE_AIRQUALITY"
    sql = sql + " where FSITE_NO= :site_no "
    param["site_no"] = search.fsiteNo
    sql = sql + " and FMONITER_YEAR= :date "
    param["date"] = search.date
    sql = sql + " and FMONITER_QUARTER= :quarter "
    param["quarter"] = search.quarter
    sql = sql + " order by FAQI_TYPE"
    data = db.execute(text(sql),param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['faqiName'] = data[key][0]
        d1['faqiDay'] = data[key][1]
        d1['faqiRate'] = data[key][2]
        d1['fhbDay'] = data[key][3]
        d1['fhbRate'] = data[key][4]
        d1['ftbDay'] = data[key][5]
        d1['ftbRate'] = data[key][6]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)

class AnnualAirStatisticsSearch(BaseModel):
    """每年空气统计查询请求参数"""
    fsiteNo: str = Field("", description="站点编号")
    date: str = Field("2000", description="统计年份，默认2000")

@data_air_report2_router.get("/getYearStatisticsDataByFsiteNo",summary="每年空气统计")
def list_air_report003(search:AnnualAirStatisticsSearch=Query(),db:Session = Depends(get_db)):
    param = {}
    sql = "select faqi_name,faqi_day,faqi_rate,fhb_day,fhb_rate,ftb_day,ftb_rate"
    sql = sql + " from A_TY_SITE_AIRQUALITY"
    sql = sql + " where FSITE_NO= :site_no "
    param["site_no"] = search.fsiteNo
    sql = sql + " and FMONITER_YEAR= :date "
    param["date"] = search.date
    sql = sql + " order by FAQI_TYPE"
    data = db.execute(text(sql),param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['faqiName'] = data[key][0]
        d1['faqiDay'] = data[key][1]
        d1['faqiRate'] = data[key][2]
        d1['fhbDay'] = data[key][3]
        d1['fhbRate'] = data[key][4]
        d1['ftbDay'] = data[key][5]
        d1['ftbRate'] = data[key][6]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)

class MonthlyAirIndexSearch(BaseModel):
    """每月空气指数查询请求参数"""
    fsiteNo: str = Field("", description="站点编号")
    month: str = Field("200001", description="统计年月，格式为YYYYMM，默认200001")

@data_air_report2_router.get("/getMonthExponentData",summary="每月空气指数")
def list_air_report004(search:MonthlyAirIndexSearch=Query(),db:Session = Depends(get_db)):
    sql = "select fitem_name,fitem_value,fitem_range,fhb_value,fhb_rate,fhb_flag,ftb_value,ftb_rate,ftb_flag"
    sql = sql + " from a_tm_site_airitem"
    sql = sql + " where fsite_no= :fsiteNo "
    sql = sql + " and fmoniter_month= :month "
    sql = sql + " and fitem_name in ('O3','PM2.5','PM10','CO','NO2','SO2')"
    sql = sql + " order by fitem_name"
    data = db.execute(text(sql),{
        "fsiteNo": search.fsiteNo,
        "month": search.month,
    }).fetchall()
    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fitem_name'] = data[key][0]
        d1['fitem_value'] = data[key][1]
        d1['fitem_range'] = data[key][2]
        d1['fhb_value'] = data[key][3]
        d1['fhb_rate'] = data[key][4]
        d1['fhb_flag'] = data[key][5]
        d1['ftb_value'] = data[key][6]
        d1['ftb_rate'] = data[key][7]
        d1['ftb_flag'] = data[key][8]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)

class MonthlyAirQualityIndexSearch(BaseModel):
    """每月空气指数查询请求参数"""
    fsiteNo: str = Field("", description="站点编号")
    month: str = Field("2000-01", description="统计年月，格式为YYYY-MM，默认2000-01")

@data_air_report2_router.get("/getMonthExponentChartData",summary="每月空气指数")
def list_air_report005(search:MonthlyAirQualityIndexSearch = Query(),db:Session = Depends(get_db)):
    sql = "select * from proc_rpt_airreport009( :fsiteNo , :month )"
    data = db.execute(text(sql),{
        "fsiteNo": search.fsiteNo,
        "month": search.month
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fentryid'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fsiteName'] = data[key][2]
        d1['faqi1'] = data[key][3]
        d1['ftype1'] = data[key][4]
        d1['fmain1'] = data[key][5]
        d1['faqi2'] = data[key][6]
        d1['ftype2'] = data[key][7]
        d1['fmain2'] = data[key][8]
        d1['faqi3'] = data[key][9]
        d1['ftype3'] = data[key][10]
        d1['fmain3'] = data[key][11]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)

class QuarterlyAirIndexSearch(BaseModel):
    """每季空气指数查询请求参数"""
    fsiteNo: str = Field("", description="站点编号")
    year: str = Field("2000", description="统计年份，默认2000")
    quarter: str = Field("1", description="统计季度，默认1季度")

@data_air_report2_router.get("/getQuarterExponentData",summary="每季空气指数")
def list_air_report006(search:QuarterlyAirIndexSearch=Query(),db:Session = Depends(get_db)):
    sql = "select fitem_name,fitem_value,fitem_range,fhb_value,fhb_rate,fhb_flag,ftb_value,ftb_rate,ftb_flag"
    sql = sql + " from a_tq_site_airitem"
    sql = sql + " where fsite_no= :fsiteNo "
    sql = sql + " and fmoniter_year= :year "
    sql = sql + " and fmoniter_quarter= :quarter "
    sql = sql + " and fitem_name in ('O3','PM2.5','PM10','CO','NO2','SO2')"
    sql = sql + " order by fitem_name"
    data = db.execute(text(sql),{
        "fsiteNo": search.fsiteNo,
        "year": search.year,
        "quarter": search.quarter,
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fitem_name'] = data[key][0]
        d1['fitem_value'] = data[key][1]
        d1['fitem_range'] = data[key][2]
        d1['fhb_value'] = data[key][3]
        d1['fhb_rate'] = data[key][4]
        d1['fhb_flag'] = data[key][5]
        d1['ftb_value'] = data[key][6]
        d1['ftb_rate'] = data[key][7]
        d1['ftb_flag'] = data[key][8]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)

class QuarterlyAirQualityIndexSearch(BaseModel):
    """每季空气指数查询请求参数"""
    fsiteNo: str = Field("", description="站点编号")
    year: str = Field("2000", description="统计年份，默认2000")
    quarter: str = Field("1", description="统计季度，默认1季度")

@data_air_report2_router.get("/getQuarterExponentChartData",summary="每月空气指数")
def list_air_report007(search:QuarterlyAirQualityIndexSearch=Query(),db:Session = Depends(get_db)):
    sql = "select * from proc_rpt_airreport010( :fsiteNo , :year , :quarter )"
    data = db.execute(text(sql),{
        "fsiteNo": search.fsiteNo,
        "year": search.year,
        "quarter": search.quarter
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fentryid'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fsiteName'] = data[key][2]
        d1['faqi1'] = data[key][3]
        d1['ftype1'] = data[key][4]
        d1['fmain1'] = data[key][5]
        d1['faqi2'] = data[key][6]
        d1['ftype2'] = data[key][7]
        d1['fmain2'] = data[key][8]
        d1['faqi3'] = data[key][9]
        d1['ftype3'] = data[key][10]
        d1['fmain3'] = data[key][11]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)

class AnnualAirIndexSearch(BaseModel):
    """每年空气指数查询请求参数"""
    fsiteNo: str = Field("", description="站点编号")
    year: str = Field("2000", description="统计年份，默认2000")

@data_air_report2_router.get("/getYearExponentData",summary="每年空气指数")
def list_air_report008(search:AnnualAirIndexSearch=Query(),db:Session = Depends(get_db)):
    sql = "select fitem_name,fitem_value,fitem_range,fhb_value,fhb_rate,fhb_flag,ftb_value,ftb_rate,ftb_flag"
    sql = sql + " from a_ty_site_airitem"
    sql = sql + " where fsite_no= :fsiteNo "
    sql = sql + " and fmoniter_year= :year "
    sql = sql + " and fitem_name in ('O3','PM2.5','PM10','CO','NO2','SO2')"
    sql = sql + " order by fitem_name"
    data = db.execute(text(sql),{
        "fsiteNo": search.fsiteNo,
        "year": search.year,
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fitem_name'] = data[key][0]
        d1['fitem_value'] = data[key][1]
        d1['fitem_range'] = data[key][2]
        d1['fhb_value'] = data[key][3]
        d1['fhb_rate'] = data[key][4]
        d1['fhb_flag'] = data[key][5]
        d1['ftb_value'] = data[key][6]
        d1['ftb_rate'] = data[key][7]
        d1['ftb_flag'] = data[key][8]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)

class AnnualAirQualitySearch(BaseModel):
    """每年空气指数查询请求参数"""
    fsiteNo: str = Field("", description="站点编号")
    year: str = Field("2000", description="统计年份，默认2000")

@data_air_report2_router.get("/getYearExponentChartData",summary="每年空气指数")
def list_air_report009(search:AnnualAirQualitySearch = Query(),db:Session = Depends(get_db)):
    sql = "select * from proc_rpt_airreport011( :fsiteNo , :year )"
    data = db.execute(text(sql),{
        "fsiteNo": search.fsiteNo,
        "year": search.year,
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fentryid'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fsiteName'] = data[key][2]
        d1['faqi1'] = data[key][3]
        d1['ftype1'] = data[key][4]
        d1['fmain1'] = data[key][5]
        d1['faqi2'] = data[key][6]
        d1['ftype2'] = data[key][7]
        d1['fmain2'] = data[key][8]
        d1['faqi3'] = data[key][9]
        d1['ftype3'] = data[key][10]
        d1['fmain3'] = data[key][11]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)